<?php


namespace App\Models;


use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class CarBrandStat extends Model
{
    protected $table = 'xpel_car_brand_stat';

    public function brandTotal(string $start_date, string $end_date)
    {
        $sql = 'SELECT car_brand FROM xpel_car_brand_stat WHERE create_date>="%s" AND create_date<="%s" GROUP BY car_brand';
        $sql = sprintf($sql, $start_date, $end_date);
        $result = DB::select($sql);

        return $result ?? [];
    }

    public function mostBrand(string $start_date, string $end_date)
    {
        $sql = 'SELECT SUM(1) AS total, car_brand FROM xpel_car_brand_stat WHERE create_date>="%s" AND create_date<="%s" GROUP BY car_brand ORDER BY total DESC LIMIT 1';
        $sql = sprintf($sql, $start_date, $end_date);
        $brand = DB::select($sql);
        if (empty($brand)) {
            return [];
        }

        $car_brand = $brand[0]->car_brand;
        $sql = 'SELECT SUM(1) AS total, create_date FROM xpel_car_brand_stat WHERE create_date>="%s" AND create_date<="%s" AND car_brand="%s" GROUP BY create_date ORDER BY create_date ASC';
        $sql = sprintf($sql, $start_date, $end_date, $car_brand);
        $result = DB::select($sql);

        return [
            'list'  => $result ?? [],
            'abbr'  => $car_brand,
        ];
    }

    public function mostModel(string $start_date, string $end_date)
    {
        $sql = 'SELECT SUM(1) AS total, car_model, car_brand FROM xpel_car_brand_stat WHERE create_date>="%s" AND create_date<="%s" GROUP BY car_model ORDER BY total DESC LIMIT 1';
        $sql = sprintf($sql, $start_date, $end_date);
        $brand = DB::select($sql);
        if (empty($brand)) {
            return [];
        }

        $car_model = $brand[0]->car_model;
        $sql = 'SELECT SUM(1) AS total, create_date FROM xpel_car_brand_stat WHERE create_date>="%s" AND create_date<="%s" AND car_model="%s" GROUP BY create_date ORDER BY create_date ASC';
        $sql = sprintf($sql, $start_date, $end_date, $car_model);
        $result = DB::select($sql);

        return [
            'list'  => $result ?? [],
            'abbr'  => $brand[0]->car_brand . ' ' . $car_model,
        ];
    }
}